﻿@page "/PageAdmin"

@using System.IO;

@using SubstrateHelper.Data;
@using SubstrateHelper.Models;
@using SubstrateHelper.Helpers;
@using OfficeOpenXml;
@using ExcelHelper;

@inject SubstrateHelperDbContext Db
<h1>数据管理</h1>
<hr />

<AuthorizeView Roles="数据库管理员">
	<Authorized>
		<h4>上传数据</h4>

		<InputFile OnChange="@LoadFiles" multiple accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" />

		<br />

		<h4>上传记录数据</h4>

		<InputFile OnChange="@LoadRecords" multiple accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" />

		<br />

		@if (isLoading)
				{
  <p>Loading...</p>
		}
		<label class="text-danger">@Message</label><br />
		<label class="text-danger">@ErrorMessage</label>

		<div class="m-5">
			<Button @onclick="SyncAllStatistics" Text="同步统计数据" />
		</div>

	</Authorized>
	<NotAuthorized>
		<AuthorizeView>
			<Authorized Context="abc">
				<div>您目前登录的账号没有包含<strong class="text-danger">数据库管理员</strong>角色。</div>
				<br />
				<div>请<a href="Identity/Account/Login">登录</a>包含<strong class="text-danger">数据库管理员</strong>角色的账号进行操作。</div>
			</Authorized>
			<NotAuthorized Context="abc">
				<label>您未登录！请<a href="Identity/Account/Login">登录</a>后进行操作。</label>
			</NotAuthorized>
		</AuthorizeView>
	</NotAuthorized>
</AuthorizeView>



<hr />
<br />
<br />
<br />
<br />
<br />
<br />

@code {


	//private Dictionary<IBrowserFile, string> loadedFiles = new Dictionary<IBrowserFile, string>();
	private long maxFileSize = 1024 * 1024 * 10;//10M
	private int maxAllowedFiles = 40;
	private bool isLoading;

	async Task LoadRecords(InputFileChangeEventArgs e)
	{
		ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
		isLoading = true;

		ErrorMessage = string.Empty;


		using (var trans = Db.Database.BeginTransaction())
		{
			try
			{
				List<string> guids = new List<string>();
				foreach (var file in e.GetMultipleFiles(maxAllowedFiles))
				{

					Message = $"读取中...@{file.Name}";
					this.StateHasChanged();

					var stream = file.OpenReadStream(maxFileSize);
					long len = stream.Length;
					byte[] buffer = new byte[len];
					await stream.ReadAsync(buffer, 0, (int)len);
					var streamMem = new MemoryStream(buffer);




					using (var package = new OfficeOpenXml.ExcelPackage(streamMem))
					{


						Message = $"正在导入数据库...@{file.Name}";
						this.StateHasChanged();

						List<string> _guids;
						var error = UpdateRecords(package, out _guids);
						if (string.IsNullOrWhiteSpace(error))
						{
							guids.AddRange(_guids);
						}
						else
						{
							Message = error;
						}

					}

					this.StateHasChanged();


				}

				//保存到数据库
				Db.SaveChanges();

				trans.Commit();

				var err = Helpers.ProcessStatisticsHelper.UpdateStatistics(guids.Distinct());
				if (string.IsNullOrWhiteSpace(err))
				{
					Message = "导入完成！";
				}
				else
				{
					Message = $"导入出错：{err}";
				}


			}
			catch (Exception ex)
			{
				trans.Rollback();
				Message = "导入数据发生错误，已回滚到初始状态。";
				ErrorMessage = ex.Message;
				while (true)
				{
					if (ex.InnerException != null)
					{
						ErrorMessage += $" ← {ex.InnerException.Message}";
						ex = ex.InnerException;
					}
					else
					{
						break;
					}
				}
			}
		}



		isLoading = false;
	}

	async Task LoadFiles(InputFileChangeEventArgs e)
	{
		ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
		isLoading = true;

		ErrorMessage = string.Empty;


		using (var trans = Db.Database.BeginTransaction())
		{
			try
			{
				foreach (var file in e.GetMultipleFiles(maxAllowedFiles))
				{

					Message = $"读取中...@{file.Name}";
					this.StateHasChanged();

					var stream = file.OpenReadStream(maxFileSize);
					long len = stream.Length;
					byte[] buffer = new byte[len];
					await stream.ReadAsync(buffer, 0, (int)len);
					var streamMem = new MemoryStream(buffer);




					using (var package = new OfficeOpenXml.ExcelPackage(streamMem))
					{
						Tables = EPPlusBuilder.Parse(package, FirstRowIsHeader);

						Message = $"正在导入数据库...@{file.Name}";
						this.StateHasChanged();

						UpdateDatabase(Tables);
					}

					this.StateHasChanged();


				}

				//保存到数据库
				Db.SaveChanges();
				trans.Commit();
				Message = "导入完成！";
			}
			catch (Exception ex)
			{
				trans.Rollback();
				Message = "导入数据发生错误，已回滚到初始状态。";
				ErrorMessage = ex.Message;
				while (true)
				{
					if (ex.InnerException != null)
					{
						ErrorMessage += $" ← {ex.InnerException.Message}";
						ex = ex.InnerException;
					}
					else
					{
						break;
					}
				}
			}
		}



		isLoading = false;
	}


	public string Message { get; set; }
	public string ErrorMessage { get; set; }
	public bool FirstRowIsHeader { get; set; } = true;

	EPPlusBuilder.TablesModel Tables = new EPPlusBuilder.TablesModel();

	[CascadingParameter]
	public ComponentMessageBox messageBoxComponent { get; set; }


	string UpdateRecords(ExcelPackage package, out List<string> guids)
	{
		guids = new List<string>();
		var sheet = package.Workbook.Worksheets[0];
		try
		{
			//



			//以第3行判断参数数量，并获得参数名列表
			int col = 1;
			List<string> paraNames = new List<string>();

			int colLot = 0;//主批号所在列
			int colOutLot = 0;//产出标识所在列
			while (true)
			{
				var val = sheet.Cells[3, col].Value;
				if (val == null)
				{
					var valBottom = sheet.Cells[4, col].Value;
					if (valBottom != null)
					{
						var strBottom = valBottom.ToString();
						if (strBottom == "检验标准")
						{
							col+=4;
							continue;//这是参数里的检验项，不添加为参数
						}
					}
					else
					{
						break;
					}
					
				}
				var paraName = val.ToString();
				if (string.IsNullOrWhiteSpace(paraName))
				{
					break;
				}
				if (paraName == "主批号")
				{
					colLot = col;
				}
				else if (paraName == "产出标识")
				{
					colOutLot = col;
				}



				paraNames.Add(paraName);

				col++;

			}

			if (colLot <= 0 || colOutLot <= 0)
			{
				return $"{sheet.Name}:没有主批号或者产出标识";//如果没有主批号或者产出标识，则返回空
			}



			//获得对应的参数列表
			List<ProcessBase.Models.Parameter> paras = new List<ProcessBase.Models.Parameter>();
			foreach (var paraName in paraNames)
			{
				var para = Db.Parameters.Where(x => x.ParameterName == paraName && x.ProcessName == sheet.Name).FirstOrDefault();
				paras.Add(para);
			}

			if (paras.Count <= 0)
			{
				return $"{sheet.Name}:无参数";
			}


			//
			int row = 5;
			while (true)
			{
				var valLot = sheet.Cells[row, colLot].Value;
				var valOutLot = sheet.Cells[row, colOutLot].Value;
				if (valLot == null)
				{
					break; //没有记录了
				}



				if (valOutLot == null)
				{
					row++;
					continue; //没有产出标识，跳过
				}

				var strLot = valLot.ToString();
				var strOutLot = valOutLot.ToString();

				if (string.IsNullOrWhiteSpace(strLot) || string.IsNullOrWhiteSpace(strOutLot))
				{
					break;//没有记录了
				}

				//guid
				var guid = Db.ProcessStatistics.Where(x => x.MainLotNo == strLot && x.LotNoOutput == strOutLot && x.ProcessName == sheet.Name).Select(x => x.Guid).FirstOrDefault();
				if (string.IsNullOrWhiteSpace(guid))
				{
					guid = Guid.NewGuid().ToString();
				}

				


				guids.Add(guid);

				col = 1;
				foreach (var para in paras)
				{


					//记录是否已经存在
					var record = Db.Records.Where(x => x.Guid == guid && x.ProcessName == sheet.Name && x.ParameterName == para.ParameterName).FirstOrDefault();
					if (record == null)
					{
						record = new ProcessBase.Models.Record();
						record.Guid = guid;
						record.ProcessName = sheet.Name;
						record.ParameterName = para.ParameterName;
						record.Date = DateTime.Now;

						Db.Add(record);//没有记录则插入记录
					}

					record.MainLot = strLot;
					record.OutLot = strOutLot;

					var valObj = sheet.Cells[row, col].Value;

					if (para.ParameterType == ProcessBase.Models.ParameterType.Date || para.ParameterType == ProcessBase.Models.ParameterType.Time)
					{
						if (valObj != null)
						{

							record.DateTimeValue = (DateTime)ExcelHelper.StringParser.Parse(typeof(DateTime), valObj.ToString());
						}
						else
						{
							record.DateTimeValue = null;
						}

					}
					else if (para.ParameterType == ProcessBase.Models.ParameterType.Number)
					{
						if (valObj != null)
						{
							double dval;
							var sucess = double.TryParse(valObj.ToString(), out dval);
							if (sucess)
							{
								record.NumberValue = dval;
							}
							else
							{
								record.NumberValue = null;
							}

						}
						else
						{
							record.NumberValue = null;
						}


					}
					else
					{
						if (valObj != null)
						{
							record.StringValue = valObj.ToString();
						}
						else
						{
							record.StringValue = null;
						}

					}

					//如果是检验项目，则包含三个子项
					if (para.IsCheckPoint)
					{
						var valStandard = sheet.Cells[row, col + 1].Value;
						var valHeGeLv = sheet.Cells[row, col + 2].Value;

						var valInspector = sheet.Cells[row, col + 3].Value;
						var valCheckState = sheet.Cells[row, col + 4].Value;

						//列增加3
						col += 4;
						if (valStandard != null)
						{
							record.CheckStandard = valStandard.ToString();
						}
						else
						{
							record.CheckStandard = null;
						}

						if (valHeGeLv != null)
						{
							record.CheckResult = double.Parse(valHeGeLv.ToString());
						}
						else
						{
							record.CheckResult = null;
						}

						if (valInspector != null)
						{
							record.Inspector = valInspector.ToString();
						}
						else
						{
							record.Inspector = null;
						}

						if (valCheckState != null)
						{
							record.QualityState = valCheckState.ToString();
						}
						else
						{
							record.QualityState = null;
						}
					}

					


					col++;
				}

				row++;

			}
			Db.SaveChanges();





			return null;
		}
		catch (Exception ex)
		{
			return $"出错@{sheet.Name}:{ex.Message}";
		}







	}


	void UpdateDatabase(EPPlusBuilder.TablesModel tables)
	{
		foreach (var table in tables.Tables)
		{
			var name = table.Name;
			var header = table.Header;
			var body = table.Body;

			//db
			var dbPropertyInfo = Db.GetType().GetProperty(name);
			if (dbPropertyInfo == null)
			{
				continue;
			}

			//dbset
			var dbsetType = dbPropertyInfo.PropertyType;
			if (!dbsetType.IsGenericType)
			{
				continue;
			}
			var dbset = dbPropertyInfo.GetValue(Db) as IEnumerable<object>;

			//item
			var args = dbsetType.GetGenericArguments();
			if (args == null || args.Length < 1)
			{
				continue;
			}
			var itemType = args[0];


			//获得表头长度（直接用header.Cells.Count()，在表头右边有空格时会出错）
			int colCount = 0;
			for (int i = 0; i < header.Cells.Count(); i++)
			{
				colCount++;

				string strHeader = header.Cells[i].Value;
				if (strHeader == "操作")//“操作”总是最后一列
				{
					break;
				}
			}

			//
			int rowIndex = 0;
			foreach (var row in body)
			{
				rowIndex++;


				//操作类型：增加、删除、修改
				string change = row.Cells[colCount - 1].Value;
				if (string.IsNullOrEmpty(change))//"操作"列为""的表示不处理
				{
					continue;
				}

				//从excel生产的item
				var itemFromExcel = Activator.CreateInstance(itemType);
				if (itemFromExcel == null)
				{
					continue;
				}

				//赋值
				for (int i = 0; i < colCount - 1; i++)
				{
					string properName = header.Cells[i].Value;
					string strValue = row.Cells[i].Value;
					if (!string.IsNullOrEmpty(strValue))
					{
						try
						{
							var propertyTypeInfo = itemType.GetProperty(properName);
							if (propertyTypeInfo != null)
							{
								propertyTypeInfo.SetValue(itemFromExcel, StringParser.Parse(propertyTypeInfo.PropertyType, strValue));
							}

						}
						catch (Exception ex1)
						{
							//非数据列忽略
						}
					}

				}

				//
				if (change == "增加")
				{


					//加入dbset
					var methodInfo = dbsetType.GetMethod("Add");
					object?[] paras = new object?[1];
					paras[0] = itemFromExcel;
					methodInfo.Invoke(dbset, paras);
				}
				else if (change == "删除")
				{
					object objFind = Find(dbset, itemFromExcel, itemType);
					if (objFind == null)
					{
						throw (new Exception($"表<{name}>第{rowIndex}行，在执行“{change}操作时找不到原记录！\n请注意不要修改主键的列。”"));
					}

					//删除
					var methodInfo = dbsetType.GetMethod("Remove");
					object?[] paras = new object?[1];
					paras[0] = objFind;
					methodInfo.Invoke(dbset, paras);

				}
				else if (change == "修改")
				{
					object objFind = Find(dbset, itemFromExcel, itemType);
					if (objFind == null)
					{
						throw (new Exception($"表<{name}>第{rowIndex}行，在执行“{change}操作时找不到原记录！\n请注意不要修改主键的列。”"));
					}

					//修改
					for (int i = 0; i < colCount - 1; i++)
					{
						string properName = header.Cells[i].Value;
						string strValue = row.Cells[i].Value;
						if (!string.IsNullOrEmpty(strValue))
						{
							var propertyTypeInfo = itemType.GetProperty(properName);
							propertyTypeInfo.SetValue(objFind, StringParser.Parse(propertyTypeInfo.PropertyType, strValue));
						}

					}
				}
				else if (change == "电性能录入")
				{
					SubstrateInfo info = itemFromExcel as SubstrateInfo;
					if (info != null)
					{
						var infoDb = Db.SubstrateInfos.Where(x => x.LotNo == info.LotNo && x.PartNo == info.PartNo).FirstOrDefault();
						if (infoDb == null)//新增
						{
							Db.Add(info);
						}
						else//修改
						{
							infoDb.CopyFrom(info);
						}
					}
				}


			}


		}






	}

	bool MatchKeys(object objDb, object objExcel, Type typeItem)
	{
		//这种方法有时候，名称不对（长名字后面会变成`1）
		//Microsoft.EntityFrameworkCore.Metadata.IEntityType entityType = Db.Model.FindEntityType(typeItem.FullName);

		//要这样
		Microsoft.EntityFrameworkCore.Metadata.IEntityType entityType = null;
		var types = Db.Model.GetEntityTypes();
		foreach (var t in types)
		{
			if (t.ClrType == typeItem)
			{
				entityType = t;
			}
		}
		if (entityType == null)
		{
			return false;
		}




		//键
		var keys = entityType.GetKeys();
		foreach (var key in keys)
		{
			//IKey.Properties存储了键的列，组合键有多个列
			foreach (var prop in key.Properties)
			{
				//键列名
				var keyname = prop.Name;

				//对比
				var propertyInfo = typeItem.GetProperty(keyname);
				if (propertyInfo == null)
				{
					return false;
				}
				var value1 = propertyInfo.GetValue(objDb);
				var value2 = propertyInfo.GetValue(objExcel);
				if (!value1.Equals(value2))
				{

					return false;
				}
			}

		}
		return true;
	}

	object Find(object dbset, object objExcel, Type typeItem)
	{
		var querable = dbset as IQueryable<object>;
		try
		{
			var query = querable.AsEnumerable().Where(obj => MatchKeys(obj, objExcel, typeItem)).First();
			return query;
		}
		catch (Exception ex)
		{
			return null;
		}

	}

	public async Task ResetDatabase()
	{
		var re = await messageBoxComponent.ShowMessageBox("确定要清除所有数据，重置数据库吗？", "警告", yesHtml: "是", noHtml: "否");
		if (re == ComponentMessageBox.MessageBoxReturn.Yes)
		{
			try
			{
				await Db.Database.EnsureDeletedAsync();
				await Db.Database.EnsureCreatedAsync();
			}
			catch (Exception ex)
			{
				Message = ex.Message;
			}

		}
	}




	async private void SyncAllStatistics()
	{
		Message = "同步中..";
		this.StateHasChanged();
		await Task.Run(() =>
		{


			var guids = Db.Records.Select(x => x.Guid).Distinct();
			foreach (var guid in guids)
			{
				var st = Helpers.ProcessStatisticsHelper.MakeStatistics(guid);
				if (st != null)
				{
					var old = Db.ProcessStatistics.Where(x => x.Guid == st.Guid).FirstOrDefault();
					if (old == null)
					{
						Db.ProcessStatistics.Add(st);
					}
					else
					{
						old.CopyFrom(st);
					}


				}
			}
			Db.SaveChanges();
		});

		Message = "同步完成!";
		this.StateHasChanged();
	}
}
